﻿using System;
using System.Collections.Generic;
using System.Text;
using Common;
using System.Data;
using System.Data.SqlClient;
namespace DA
{
    public class DBuserss : IDisposable
    {
        string sql;
        SqlHelpers sqlh;
        DataSet ds;
        // SqlDataReader dr;
        public DBuserss()
        {
            sqlh = new SqlHelpers();
        }
        public DBuserss(SqlHelpers sh)
        {
            sqlh = sh;
        }
        public void Dispose()
        {
            sqlh.Dispose();
        }


        /// <summary>
        /// 验证用户登录的方法
        /// </summary>
        /// <param name="u"></param>
        /// <returns></returns>
        public DataSet DBUser(CUsers u)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@uName", SqlDbType.VarChar, 20));
            pars[0].Value = u.CuName1;
            pars.Add(new SqlParameter("@uPwd", SqlDbType.VarChar, 30));
            pars[1].Value = u.CuPwd1;
            sql = "select * from Users where uName=@uName and uPwd=@uPwd";
            ds = sqlh.ExcuteSelect(sql, "Users", pars);
            return ds;
        }

        /// <summary>
        /// 获取用户信息
        /// </summary>
        /// <param name="u"></param>
        /// <returns></returns>
        public DataSet getUserInfo(CUsers u)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@uName", SqlDbType.VarChar, 20));
            pars[0].Value = u.CuName1;
            pars.Add(new SqlParameter("@uPwd", SqlDbType.VarChar, 30));
            pars[1].Value = u.CuPwd1;
            sql = "select a.*,b.rName from Users a inner join Roles b on a.rId=b.rId where a.uName=@uName and a.uPwd=@uPwd";
            ds = sqlh.ExcuteSelect(sql, "UserInfo", pars);
            return ds;
        }

        /// <summary>
        /// 查询所有用户的方法
        /// </summary>
        /// <returns></returns>
        public DataSet DBAllUsers()
        {
            sql = "select u.uId,u.uName,u.uPwd,r.rName from Users u inner join Roles r on u.rId=r.rId";
            ds = sqlh.ExcuteSelect(sql, "UR", null);
            return ds;
        }

        /// <summary>
        /// 插入用户信息的方法
        /// </summary>
        /// <param name="u"></param>
        public void DBUsersInsert(CUsers u)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@uName", SqlDbType.VarChar, 20));
            pars[0].Value = u.CuName1;
            pars.Add(new SqlParameter("@uPwd", SqlDbType.VarChar, 30));
            pars[1].Value = u.CuPwd1;
            pars.Add(new SqlParameter("@rId", SqlDbType.Int));
            pars[2].Value = u.CuId1;
            sql = "insert into Users values(@uName,@uPwd,@rId)";
            sqlh.NonQuery(sql, pars, CommandType.Text);
        }

        /// <summary>
        /// 修改用户信息的方法
        /// </summary>
        /// <param name="u"></param>
        public void DBUsersUpdate(CUsers u)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@uId", SqlDbType.Int));
            pars[0].Value = u.CuId1;
            pars.Add(new SqlParameter("@uName", SqlDbType.VarChar, 20));
            pars[1].Value = u.CuName1;
            pars.Add(new SqlParameter("@uPwd", SqlDbType.VarChar, 30));
            pars[2].Value = u.CuPwd1;
            pars.Add(new SqlParameter("@rId", SqlDbType.Int));
            pars[3].Value = u.CrId1;
            sql = "update Users set uName=@uName,uPwd=@uPwd,rId=@rId where uId=@uId";
            sqlh.NonQuery(sql, pars, CommandType.Text);
        }

        /// <summary>
        /// 删除用户信息的方法
        /// </summary>
        /// <param name="uId">用户编号</param>
        public void DBUsersDelete(int uId)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@uId", SqlDbType.Int));
            pars[0].Value = uId;
            sql = "delete from Users where uId=@uId";
            sqlh.NonQuery(sql, pars, CommandType.Text);
        }

        /// <summary>
        /// 根据用户编号查询用户显示控件的方法
        /// </summary>
        /// <param name="uId"></param>
        /// <returns></returns>
        public DataSet DBUserControls(int uId)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@uId", SqlDbType.Int));
            pars[0].Value = uId;
            //sql = "select c.cControlName from Users u inner join Roles r on u.rId=r.rId inner join RolesMenu rm on r.rId=rm.rId inner join menusTiao mt on rm.mId=mt.mId inner join Controls c on mt.mId=c.mId where u.uId=@uId";
            sql = "select c.cControlName from Users u inner join Roles r on u.rId=r.rId inner join RolesMenu rm on r.rId=rm.rId inner join menusTiao mt on rm.mId=mt.mId inner join Controls c on mt.mId=c.mId where u.uId=@uId";

            ds = sqlh.ExcuteSelect(sql, "URPC", pars);
            return ds;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="username"></param>
        /// <param name="oldpwd"></param>
        /// <param name="newpwd"></param>
        /// <returns></returns>
        public void ChangePwd(string username, string oldpwd, string newpwd)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@uName", SqlDbType.VarChar, 20));
            pars[0].Value = username;
            pars.Add(new SqlParameter("@uPwd", SqlDbType.VarChar, 30));
            pars[1].Value = oldpwd;
            pars.Add(new SqlParameter("@newPwd", SqlDbType.VarChar, 30));
            pars[2].Value = newpwd;
            sql = "update Users set uPwd=@newPwd where uName=@uName and uPwd=@uPwd";
            sqlh.NonQuery(sql, pars, CommandType.Text);

        }

    }
}
